﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using Volo.Abp;

namespace Mozhi.Abp.Core.Offices.Excels
{
    public class NPOIExcelHandler : IExcelHandler
    {
        public Dictionary<CellValueType, ICellStyle> _cellStyleDicts;

        public Stream GetExcelFileStream(ExcelDocument document)
        {
            var workbook = GetWorkbook(document.FileType);

            foreach (var excelSheet in document.Sheets)
            {
                var sheet = GetWorksheet(workbook, excelSheet);

                HandleWorksheet(workbook, sheet, excelSheet);
            }

            NPOIMemoryStream stream = new NPOIMemoryStream();
            stream.AutoClose = false;

            workbook.Write(stream);
            stream.Flush();
            stream.Seek(0, SeekOrigin.Begin);
            stream.AutoClose = true;

            return stream;
        }

        public DataSet GetDataSet(Stream stream)
        {
            IWorkbook workbook = WorkbookFactory.Create(stream);

            var sheetCount = workbook.NumberOfSheets;

            if (sheetCount == 0)
                throw new BusinessException("10000", "不能上传空文件");


            DataSet ds = new DataSet();

            for (var i=0; i < sheetCount; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);

                var dataTable = GetDataTable(sheet);

                ds.Tables.Add(dataTable);
            }

            return ds;
        }



        #region 导出相关
        private IWorkbook GetWorkbook(FileType fileType)
        {
            IWorkbook workbook;

            if (fileType == FileType.XLSX)
                workbook = new XSSFWorkbook();
            else
                workbook = new HSSFWorkbook();

            return workbook;
        }


        private ISheet GetWorksheet(IWorkbook workbook, ExcelSheet excelSheet)
        {
            var sheetName = !excelSheet.SheetName.IsNullOrEmpty() ? excelSheet.SheetName : $"sheet{workbook.NumberOfSheets}";

            ISheet sheet = workbook.CreateSheet(sheetName);

            return sheet;
        }

        private void HandleWorksheet(IWorkbook workbook, ISheet sheet, ExcelSheet excelSheet)
        {
            //表头  
            IRow header = sheet.CreateRow(sheet.FirstRowNum);

            var columns = excelSheet.Columns;

            IDrawing drawing = sheet.CreateDrawingPatriarch();

            for (int i = 0; i < columns.Count; i++)
            {
                var headerCell = header.CreateCell(i);

                var column = columns[i];

                headerCell.SetCellValue(column.Caption);

                sheet.SetColumnWidth(i + 1, (column.Width == 0 ? 20 : column.Width) * 256);

                if (!column.Field.IsNullOrEmpty())
                {
                    IComment columnComment = drawing.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, i + 1, 0, i+2, 3));
                    columnComment.String = new XSSFRichTextString(column.Field);
                    headerCell.CellComment = columnComment;
                }
            }


            //数据
            if (excelSheet.Rows == null || excelSheet.Rows.Count == 0)
                return;

            for (int i = 0; i < excelSheet.Rows.Count; i++)
            {
                var dataRow = excelSheet.Rows[i];

                var row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dataRow.Cells.Count; j++)
                {
                    var cell = dataRow.Cells[j];

                    var dataCell = row.CreateCell(j);

                    dataCell.SetCellValue(cell.Value);

                    var cellFormat = GetExcelCellFormat(workbook, cell.ValueType);

                    dataCell.CellStyle = cellFormat;
                }
            }
        }


        private ICellStyle GetExcelCellFormat(IWorkbook workbook, CellValueType cellValueType)
        {
            if (_cellStyleDicts == null)
                _cellStyleDicts = new Dictionary<CellValueType, ICellStyle>();

            if (_cellStyleDicts.Keys.Contains(cellValueType))
                return _cellStyleDicts[cellValueType];

            var cellStyle = workbook.CreateCellStyle();

            IDataFormat format = workbook.CreateDataFormat();

            if (cellValueType == CellValueType.Text)
                cellStyle.DataFormat = format.GetFormat("@");

            if (cellValueType == CellValueType.Number)
                cellStyle.DataFormat = format.GetFormat("0");

            if (cellValueType == CellValueType.Date)
                cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");

            if (cellValueType == CellValueType.DateTime)
                cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            if (cellValueType == CellValueType.Money)
                cellStyle.DataFormat = format.GetFormat("#,##0");

            _cellStyleDicts.Add(cellValueType, cellStyle);

            return cellStyle;
        }
        #endregion

        #region 导入相关
        private DataTable GetDataTable(ISheet sheet)
        {
            DataTable dt = new DataTable();

            var headerRow = sheet.GetRow(sheet.FirstRowNum);
            var columns = GetExcelColumns(headerRow);

            var sheetName = sheet.SheetName;

            if (columns.Count == 0)
                throw new BusinessException("10000", $"{sheetName}没有标题行");

            dt.TableName = sheetName;

            foreach (var column in columns)
            {
                dt.Columns.Add(column);
            }

            //填充数据
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < columns.Count; j++)
                {
                    dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }


        private List<string> GetExcelColumns(IRow headerRow)
        {
            var columns = new List<string>();

            for (int i = 0; i < headerRow.LastCellNum; i++)
            {
                ICell cell = headerRow.GetCell(i);

                if (cell.CellType != CellType.String)
                    continue;

                var caption = cell.StringCellValue;

                var field = cell.CellComment.String?.ToString();

                columns.Add(field.IsNullOrEmpty() ? caption : field);
            }

            return columns;
        }


        private object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion
    }
}
